﻿--use PolePosition
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_VendorVisitXMissionStructurePole_SelectForGrid')
	DROP PROCEDURE up_VendorVisitXMissionStructurePole_SelectForGrid
GO

CREATE PROCEDURE up_VendorVisitXMissionStructurePole_SelectForGrid
	 @Vendor_ProjectFK int
	,@PageSize int = 100
	,@PageNumber int = 1
	,@SearchByType varchar(50) = null
	,@SearchByText varchar(50) = null
AS

SET NOCOUNT ON	

DECLARE @StartRowNumber int
DECLARE @EndRowNumber int

SET @StartRowNumber = ((@PageNumber - 1) * @PageSize) + 1
SET @EndRowNumber = @PageNumber * @PageSize


DECLARE @table table
(
	 RowNumber int
	,TotalCount int
	,Vendor_Visit_XMissionStructures_PoleId int
	,Vendor_Visit_XMissionStructureFK int
	,XMissionStructures_PoleTopTypeFk int
	,XMissionStructures_PoleSpeciesFk int
	,XMissionStructures_PoleTreatmentFk int
	,Year int
	,XMissionStructures_PoleClassFk int
	,Latitude float
	,Longitude float
	,Altitude float
	,SatelliteCount int
	,SatelliteTime datetime
	,SolutionAvailable bit
	,AID nvarchar(100)
	,LVisit int
	,Number nvarchar(10)
	,Length float
	,LVisitor nvarchar(100)
	,HaveImages int
	,Utility_Asset_XMissionStructures_PoleFk int
	,[FriendlyName] varchar(255)
	,MapFk int
	,[Section] int
	

	,Vendor_Visit_TypeFK int
	,VisitDate datetime
	,Vendor_ContactFK int
	,VendorFK int
	,VendorName varchar(50)
	,VendorContactName varchar(50)

	
)
	
INSERT @table
(
	 [RowNumber]
	,[TotalCount]
	,[Vendor_Visit_XMissionStructures_PoleId]
	,[Vendor_Visit_XMissionStructureFK]
	,[XMissionStructures_PoleTopTypeFk]
	,[XMissionStructures_PoleSpeciesFk]
	,[XMissionStructures_PoleTreatmentFk]
	,[Year]
	,[XMissionStructures_PoleClassFk]
	,[Latitude]
	,[Longitude]
	,[Altitude]
	,[SatelliteCount]
	,[SatelliteTime]
	,[SolutionAvailable]
	,[AID]
	,[LVisit]
	,[Number]
	,[Length]
	,[LVisitor]
	,[HaveImages]
	,[Utility_Asset_XMissionStructures_PoleFk]
	,[FriendlyName]
	,[MapFk]
	,[Section]
	,Vendor_Visit_TypeFK
	,VisitDate
	,Vendor_ContactFK
	,VendorFK
	,VendorName
	,VendorContactName
)
SELECT 
*
FROM 
(
	SELECT 
	     ROW_NUMBER() OVER (ORDER BY [Vendor_Visit_XMissionStructures_PoleId]) AS RowNumber
		,COUNT([Vendor_Visit_XMissionStructures_PoleId]) OVER (PARTITION BY NULL) AS TotalCount
		,Pole.[Vendor_Visit_XMissionStructures_PoleId]
		,Pole.[Vendor_Visit_XMissionStructureFK]
		,Pole.[XMissionStructures_PoleTopTypeFk]
		,Pole.[XMissionStructures_PoleSpeciesFk]
		,Pole.[XMissionStructures_PoleTreatmentFk]
		,Pole.[Year]
		,Pole.[XMissionStructures_PoleClassFk]
		,Pole.[Latitude]
		,Pole.[Longitude]
		,Pole.[Altitude]
		,Pole.[SatelliteCount]
		,Pole.[SatelliteTime]
		,Pole.[SolutionAvailable]
		,Pole.[AID]
		,Pole.[LVisit]
		,Pole.[Number]
		,Pole.[Length]
		,Pole.[LVisitor]
		,Pole.[HaveImages]
		,Pole.[Utility_Asset_XMissionStructures_PoleFk]
		,Pole.[FriendlyName]	
		,Pole.[MapFk]
		,Pole.[Section]
		,Structure.Vendor_Visit_TypeFK
		,Structure.Date AS VisitDate
		,Structure.Vendor_ContactFK
		,Contact.VendorFK
		,Vendors.Name AS VendorName	
		,Contact.Name AS VendorContactName	
		
	FROM v_tb_Vendor_Visit_XMissionStructures_Poles Pole
	INNER JOIN v_tb_Vendor_Visit_XMissionStructures Structure 
		ON Pole.Vendor_Visit_XMissionStructureFK = Structure.Vendor_Visit_XMissionStructureId
	LEFT JOIN tb_Vendor_Contacts Contact
		ON Contact.Vendor_ContactId = Structure.Vendor_ContactFK
	LEFT JOIN tb_Vendors Vendors 
		ON Vendors.VendorId = Contact.VendorFK
	
	WHERE
			Structure.Vendor_ProjectFK = @Vendor_ProjectFK
) AS Data
WHERE RowNumber BETWEEN @StartRowNumber AND @EndRowNumber


-- primary select
select * from @table





	-- VendorVisitXMissionStructuresImageCollection select

select
	v_tb_Vendor_Visit_XMissionStructures_Images.* 
from 
	v_tb_Vendor_Visit_XMissionStructures_Images 
	JOIN @table x ON v_tb_Vendor_Visit_XMissionStructures_Images.Vendor_Visit_XMissionStructures_PoleFK = x.Vendor_Visit_XMissionStructures_PoleId
	

	
 